using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Web;

using Peradigm.Framework.Domain.Exceptions;
using Peradigm.Framework.Domain.Parties;
using Peradigm.Framework.Domain.Security;
using Peradigm.Framework.Persistence;
using Peradigm.Framework.Persistence.Exceptions;

using Liberty.Domain.ApplicationManagement;
using Liberty.Domain.ApprovalManagement;
using Liberty.Domain.CaseManagement;
using Liberty.Domain.GrantManagement;
using Liberty.Domain.ServiceMemberManagement;

using Liberty.Persistence.ApprovalManagement;
using Liberty.Persistence.Kernel;
using Liberty.Persistence.CaseManagement;

namespace Liberty.Persistence.GrantManagement
{
    public class GrantBroker : CachingBroker
    {
        #region Constants
        private const string FIELD_PREFIX = "@";

        private const string
            FLD_GRANT_OID = "GrantsOID",
            FLD_GRANT_VERSION = "GrantsVersion",
            FLD_CASE_OID = "CaseOID",
            FLD_APPLICANTID = "ApplicantID",
            FLD_VALUE = "ValueOfGrant",
            FLD_CHECK_NUMBER = "CheckNumber",
            FLD_NAME_ON_CREDIT_CARD = "NameOnCreditCard",
            FLD_GRANT_TYPE = "GrantTypeID",
            FLD_GRANT_STATUS = "StateID",
            FLD_PAYMENT_TYPE = "PaymentTypeID",
            FLD_DELIVERY_METHOD = "DeliveryMethodID",
            FLD_CUSTODIAN_ID = "CustodianID",
            FLD_CUSTODIAN_ADDRESS_ID = "CustodianAddressID",
            FLD_DELIVER_TO_PERSON_ID = "DeliverToPersonID",
            FLD_DELIVER_TO_ADDRESS_ID = "DeliverToAddressID",
            FLD_PRIMARY_APPROVER = "Approver1",
            FLD_PRIMARY_APPROVAL_DATE = "ApprovalDate1",
            FLD_SECONDARY_APPROVER = "Approver2",
            FLD_SECONDARY_APPROVAL_DATE = "ApprovalDate2",
            FLD_DISPERSED_BY = "DisbursedBy",
            FLD_DISPERSAL_DATE = "DispersalDate",
            FLD_VERIFIER = "VerifiedBy",
            FLD_VERIFICATION_DATE = "VerificationDate",
            FLD_GRANT_CREATED_DATE = "GrantsCreatedDate";

        private const string
            CACHE_KEY_GRANT_STATES = "GrantStates",
            CACHE_KEY_GRANT_TYPES = "GrantTypes";

        private const string
            SP_GET_ALL_DELIVERY_METHODS = "CaseManagement.GetAllTypesOfDeliveryMethod",
            SP_GET_ALL_GRANT_STATES = "GrantManagement.GetAllGrantStates",
            SP_GET_ALL_GRANT_TYPES = "GrantManagement.GetAllGrantTypes",
            SP_GET_ALL_PAYMENT_TYPES = "CaseManagement.GetAllTypesOfPayment",
            SP_RELATIONSHIP_TYPES = "[CaseManagement].[GetAllTypesOfKinship]",
            SAVE_GRANT = "GrantManagement.SaveGrant";

        private const string GRANT_CONTEXT_OID = "Grant.OID";
        private const string ERROR_SAVE_GRANT = "Unable to save grant.";

        public const long INITIAL_STATUS = 1;
        #endregion

        #region Event Handlers
        #endregion

        #region Methods
        public List<Grant> GrantsFor( ServiceMember serviceMember )
        {
            GrantSearchCriteria criteria = new GrantSearchCriteria( serviceMember );
            List<Grant> grants = this.GrantsMatching( criteria );

            // Add all cases to the service member.
            foreach( Grant grant in grants )
            {
                Case myCase = grant.Case;
                myCase.AddGrant( grant );   // Associate the grant with the case.
                serviceMember.AddCase( myCase );
            }

            return grants;
        }

        virtual public List<Grant> GrantsMatching( GrantSearchCriteria criteria )
        {
            List<Grant> grants = new List<Grant>();
            SafeReader reader = null;
            SqlCommand cmd = null;

            try
            {
                cmd = this.CommandFor( criteria.CommandName );

                foreach( SqlParameter parameter in criteria.Parameters )
                {
                    cmd.Parameters.Add( parameter );
                }

                reader = this.ExecuteReader( cmd );
                while( reader.Read() )
                {
                    grants.Add( this.GrantFrom( reader ) );
                }
            }
            finally
            {
                this.Close( cmd );
                this.Close( reader );
            }

            return grants;
        }

        /// <summary>
        /// Answer a list of grant delivery methods, ordered based on the resultset from persistence.
        /// </summary>
        /// <returns>
        /// A List of DeliveryMethod reference values
        /// </returns>
        public List<DeliveryMethod> DeliveryMethods()
        {
            ReferenceValueBroker<DeliveryMethod> broker = new ReferenceValueBroker<DeliveryMethod>( this.ConnectionString, SP_GET_ALL_DELIVERY_METHODS );
            return broker.ActiveValues();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="oid">
        /// </param>
        /// <returns>
        /// </returns>
        public DeliveryMethod DeliveryMethodWith( long oid )
        {
            ReferenceValueBroker<DeliveryMethod> broker = new ReferenceValueBroker<DeliveryMethod>( this.ConnectionString, SP_GET_ALL_DELIVERY_METHODS );
            return broker.ValueWith( oid );
        }

        /// <summary>
        /// Answer a list of grant states, ordered based on the resultset from persistence.
        /// </summary>
        /// <returns>
        /// A List of GrantStates reference values
        /// </returns>
        public List<GrantState> GrantStates()
        {
            ReferenceValueBroker<GrantState> broker = new ReferenceValueBroker<GrantState>( this.ConnectionString, SP_GET_ALL_GRANT_STATES );
            return broker.ActiveValues();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="oid"></param>
        /// <returns></returns>
        public GrantState GrantStateWith( long oid )
        {
            ReferenceValueBroker<GrantState> broker = new ReferenceValueBroker<GrantState>( this.ConnectionString, SP_GET_ALL_GRANT_STATES );
            return broker.ValueWith( oid );
        }

        /// <summary>
        /// Answer a list of grant types ordered based on the resultset from persistence.
        /// </summary>
        /// <returns>
        /// A List of GrantTypes
        /// </returns>
        public List<GrantType> GrantTypes()
        {
            ReferenceValueBroker<GrantType> broker = new ReferenceValueBroker<GrantType>( this.ConnectionString, SP_GET_ALL_GRANT_TYPES );
            return broker.ActiveValues();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="oid">
        /// </param>
        /// <returns>
        /// </returns>
        public GrantType GrantTypeWith( long oid )
        {
            ReferenceValueBroker<GrantType> broker = new ReferenceValueBroker<GrantType>( this.ConnectionString, SP_GET_ALL_GRANT_TYPES );
            return broker.ValueWith( oid );
        }

        /// <summary>
        /// Answer a list of payment types ordered based on the resultset from persistence.
        /// </summary>
        /// <returns>
        /// A List of PaymentType reference values
        /// </returns>
        public List<PaymentType> PaymentTypes()
        {
            ReferenceValueBroker<PaymentType> broker = new ReferenceValueBroker<PaymentType>( this.ConnectionString, SP_GET_ALL_PAYMENT_TYPES );
            return broker.ActiveValues();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="oid">
        /// </param>
        /// <returns>
        /// </returns>
        public PaymentType PaymentTypeWith( long oid )
        {
            ReferenceValueBroker<PaymentType> broker = new ReferenceValueBroker<PaymentType>( this.ConnectionString, SP_GET_ALL_PAYMENT_TYPES );
            return broker.ValueWith( oid );
        }

        public List<RelationshipType> RelationshipTypes()
        {
            ReferenceValueBroker<RelationshipType> broker = new ReferenceValueBroker<RelationshipType>( base.ConnectionString, SP_RELATIONSHIP_TYPES );
            return broker.ActiveValues();
        }

        public RelationshipType RelationshipTypeWith( long oid )
        {
            ReferenceValueBroker<RelationshipType> broker = new ReferenceValueBroker<RelationshipType>( base.ConnectionString, SP_RELATIONSHIP_TYPES );
            return broker.ValueWith( oid );
        }

        public Grant Save( Grant myGrant )
        {
            SqlTransaction txn = this.NewTransaction();
            GrantBroker grantBroker;

            try
            {
                grantBroker = new GrantBroker( txn );
                grantBroker.SaveGrant( myGrant );

                this.Commit( txn );
            }
            catch( EnterpriseException ex )
            {
                this.Rollback( txn );
                ex.AddContextItem( GRANT_CONTEXT_OID, myGrant.Oid.ToString() );
                throw ex;
            }
            catch( Exception e )
            {
                this.Rollback( txn );
                ObjectNotSavedException onse = new ObjectNotSavedException( ERROR_SAVE_GRANT, e );
                if( myGrant != null )
                {
                    onse.AddContextItem( GRANT_CONTEXT_OID, myGrant.Oid.ToString() );
                }
                throw onse;
            }

            return myGrant;
        }

        #endregion

        #region Properties
        #endregion

        #region Private Methods
        private Grant GrantFrom( SafeReader reader )
        {
            // Load the case.
            long caseOid = reader.GetInt64( FLD_CASE_OID );
            Case aCase = null;
            if( this.Cases.ContainsKey( caseOid ) )
            {
                aCase = this.Cases[caseOid];
            }
            else
            {
                aCase = i_CaseBroker.CaseFrom( reader );
                this.Cases.Add( aCase.Oid, aCase );
            }

            // Load the applicant
            long applicantID = reader.GetInt64( FLD_APPLICANTID );
            Applicant applicant = i_PersonBroker.ApplicantWith( applicantID, aCase.ServiceMember );

            // Load the grant.
            Grant grant = new Grant( reader.GetInt64( FLD_GRANT_OID ),
                                     reader.GetRowVersion( FLD_GRANT_VERSION ),
                                     reader.GetDecimal( FLD_VALUE ),
                                     GrantTypeWith( reader.GetInt64( FLD_GRANT_TYPE ) ),
                                     PaymentTypeWith( reader.GetInt64( FLD_PAYMENT_TYPE ) ),
                                     DeliveryMethodWith( reader.GetInt64( FLD_DELIVERY_METHOD ) ),
#warning Load the real charity.
 Charity.TestInstance(),
                                     applicant,
                                     reader.GetDateTime( FLD_GRANT_CREATED_DATE ) );

            // Load the optional values.
            grant.CheckNumber = reader.GetInt32( FLD_CHECK_NUMBER );
            grant.NameOnCreditCard = reader.GetString( FLD_NAME_ON_CREDIT_CARD );
            long custodianID = ( reader.GetValue( FLD_DELIVER_TO_PERSON_ID ) == null ) ? 0 : reader.GetInt64( FLD_DELIVER_TO_PERSON_ID );
            if( custodianID != 0 )
            {
                grant.Custodian = i_PersonBroker.PersonWith( custodianID );
                long custodianAddressID = reader.GetInt64( FLD_DELIVER_TO_ADDRESS_ID );
                grant.CustodiansAddress = i_AddressBroker.AddressWith( custodianAddressID );
            }

            i_EventBroker.EventsFor( grant );

            aCase.AddGrant( grant );

#warning We need to get the user from the SecurityService here as well

            return grant;
        }

        private Grant SaveGrant( Grant myGrant )
        {
            SqlCommand cmd = null;
            SafeReader reader = null;

            try
            {
                Case newCase = i_CaseBroker.Save( myGrant.Case );
                newCase.AddGrant( myGrant );

                i_PersonBroker.Save( myGrant.Applicant );
                if( myGrant.Custodian != null )
                {
                    myGrant.CustodiansAddress = i_AddressBroker.Save( myGrant.CustodiansAddress );
                    i_PersonBroker.Save( myGrant.Custodian );
                }

                cmd = this.CommandFor( SAVE_GRANT );

                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_GRANT_OID, myGrant.Oid ) );
                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_GRANT_VERSION, myGrant.Version ) );
                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_CASE_OID, myGrant.Case.Oid ) );
                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_APPLICANTID, myGrant.Applicant.Oid ) );
                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_VALUE, myGrant.Value ) );
                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_GRANT_TYPE, myGrant.Type.Oid ) );
                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_GRANT_STATUS, myGrant.Status.Oid ) );
                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_PAYMENT_TYPE, myGrant.PaymentType.Oid ) );
                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_DELIVERY_METHOD, myGrant.DeliveryMethod.Oid ) );

                // Optional Parameters
                if( myGrant.Custodian != null )
                {
                    cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_CUSTODIAN_ID, myGrant.Custodian.Oid ) );
                    cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_CUSTODIAN_ADDRESS_ID, myGrant.CustodiansAddress.Oid ) );
                }
                if( myGrant.CheckNumber > 0 )
                {
                    cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_CHECK_NUMBER, myGrant.CheckNumber ) );
                }
                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_NAME_ON_CREDIT_CARD, myGrant.NameOnCreditCard ) );

                // Execute and read oid/timestamp
                reader = this.ExecuteReader( cmd );
                if( reader.Read() )
                {
                    myGrant.Oid = reader.GetInt64( FLD_GRANT_OID );
                    myGrant.Version = reader.GetRowVersion( FLD_GRANT_VERSION );
                }
                else
                {
                    throw new ObjectNotSavedException( ERROR_SAVE_GRANT );
                }

                i_EventBroker.SaveEvents( myGrant );
            }
            finally
            {
                this.Close( reader );
                this.Close( cmd );
            }

            return myGrant;
        }
        #endregion

        #region Private Properties
        private Dictionary<long, Case> Cases
        {
            get
            {
                if( i_Cases == null )
                {
                    this.i_Cases = new Dictionary<long, Case>();
                }
                return i_Cases;
            }
        }
        #endregion

        #region Construction and Finalization
        public GrantBroker( SqlTransaction txn )
            : base( txn )
        {
            this.RegisterKey( CACHE_KEY_GRANT_STATES );
            this.RegisterKey( CACHE_KEY_GRANT_TYPES );

            i_CaseBroker = new CaseBroker( txn );
            i_EventBroker = new EventBroker( txn );
            i_PersonBroker = new PersonBroker( txn );
            i_AddressBroker = new AddressBroker( txn );
        }

        public GrantBroker( string cxnString )
            : base( cxnString )
        {
            this.RegisterKey( CACHE_KEY_GRANT_STATES );
            this.RegisterKey( CACHE_KEY_GRANT_TYPES );

            i_CaseBroker = new CaseBroker( this.ConnectionString );
            i_EventBroker = new EventBroker( this.ConnectionString );
            i_PersonBroker = new PersonBroker( this.ConnectionString );
            i_AddressBroker = new AddressBroker( this.ConnectionString );
        }

        ~GrantBroker()
        {
            this.UnregisterKey( CACHE_KEY_GRANT_STATES );
            this.UnregisterKey( CACHE_KEY_GRANT_TYPES );
        }
        #endregion

        #region Data Elements
        AddressBroker i_AddressBroker = null;
        CaseBroker i_CaseBroker = null;
        EventBroker i_EventBroker = null;
        PersonBroker i_PersonBroker = null;
        Dictionary<long, Case> i_Cases = null;
        #endregion
    }
}
